package com.bestseller.poi.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.bestseller.poi.entity.Arrival;
import com.bestseller.poi.mapper.ArrivalMapper;

@Service
public class ArrivalService {
	
	@Autowired
	private ArrivalMapper arrivalMapper;
	
	
	@Transactional
	public List<String> upload(File file) throws DuplicateKeyException{
		InputStream is=null;
		Workbook wb=null;
		try {
			 is=new FileInputStream(file);
			 wb=WorkbookFactory.create(is);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Sheet sheet=null;
		sheet=wb.getSheet("上新");
		if(sheet!=null){
			// 3. 解析数据: 解析每一行, 每一个单元格.
			List<Arrival> arrivals=new ArrayList<>();
			List<String> errors = parseToArrivalList(sheet,arrivals);
			if(errors.size()<=0){
				//批量插入
				Arrival arrival=null;
				for(int i=0;i<arrivals.size();i++){
					arrival=arrivals.get(i);
					if(arrivalMapper.getById(arrival).size()>0){
						arrivalMapper.deleteAll(arrival);
						continue;
					}
				}
				
				if(arrivals.size()>500){
					List<Arrival> arrivalList=new LinkedList<>();
					for(int i=0;i<arrivals.size();i++){
						arrivalList.add(arrivals.get(i));
						if(i%500==0){
						
							arrivalMapper.batchSave(arrivalList);
							arrivalList.clear();
						}
						if(i==arrivals.size()-1&&i%500!=0){
							arrivalMapper.batchSave(arrivalList);
						}
					}
				}else{
						arrivalMapper.batchSave(arrivals);
				}
				return new ArrayList<>();
			}else{
				return errors;
			}
		}
		
		return null;
	}

	private List<String> parseToArrivalList(Sheet sheet, List<Arrival> arrivals) {
		List<String> errors=new ArrayList<>();
		for(int i=2;i<=sheet.getLastRowNum();i++){
			Row row=sheet.getRow(i);
			Arrival arrival=paseToArrival(row,i+1,errors);
			if(arrival!=null&&arrival.getShopCode()!=null){
				arrivals.add(arrival);
			}
		}
		return errors;
	}

	private Arrival paseToArrival(Row row,int i,List<String> errors) {
		Arrival arrival=null;
		Cell cell=row.getCell(0);
		String shopCode=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			if(!getCellValue(cell).trim().equals("")){
				shopCode=getCellValue(cell);
			}else{
				errors.add("导入失败,shopCode为空,第"+i+"行第1列");
			}
		}
		
		cell=row.getCell(1);
		String arrivalDate=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			if(!getCellValue(cell).trim().equals("")){
				arrivalDate=getCellValue(cell);
			}else{
				errors.add("导入失败,arrivalDate为空,第"+i+"行第2列");
			}
		}
		
		cell=row.getCell(2);
		String styleCode=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			if(!getCellValue(cell).trim().equals("")){
				styleCode=getCellValue(cell);
			}else{
				errors.add("导入失败,styleCode为空,第"+i+"行第3列");
			}
		}
		
		cell=row.getCell(3);
		String styleColor=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			if(!getCellValue(cell).trim().equals("")){
				styleColor=getCellValue(cell);
			}else{
				errors.add("导入失败,styleColor为空,第"+i+"行第4列");
			}
		}
		
		cell=row.getCell(4);
		String editorEmail=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			if(!getCellValue(cell).trim().equals("")){
				editorEmail=getCellValue(cell);
			}else{
				errors.add("导入失败,为空,第"+i+"行第5列");
			}
		}
		
		cell=row.getCell(5);
		String productEditor1=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			if(!getCellValue(cell).trim().equals("")){
				productEditor1=getCellValue(cell);
			}else{
				errors.add("导入失败,为空,第"+i+"行第6列");
			}
		}
		
		cell=row.getCell(6);
		String productEditor2=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			if(!getCellValue(cell).trim().equals("")){
				productEditor2=getCellValue(cell);
			}else{
				errors.add("导入失败,为空,第"+i+"行第7列");
			}
		}
		
		arrival=new Arrival(shopCode, arrivalDate, styleCode, styleColor, editorEmail, productEditor1, productEditor2);
		
		return arrival;
	}
	
	//解析单元格 的类型
		private DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
		private String getCellValue(Cell cell) {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				return cell.getRichStringCellValue().getString();
			case Cell.CELL_TYPE_NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					return dateFormat.format(date)+"";
				} else {
					return cell.getNumericCellValue() + "";
				}
			case Cell.CELL_TYPE_BOOLEAN:
				return cell.getBooleanCellValue() + "";
			case Cell.CELL_TYPE_FORMULA:
				return cell.getCellFormula() + "";
			}
			return null;
		}
}
